package org.farec.tcc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.farec.tcc.modelo.Local;

public class LocalDAO {
	
	private Connection connection;
	
	public LocalDAO(Connection connection)
	{
		this.connection=connection;
	}
	
	public void adiciona(Local local)
	{
		String sql = "INSERT INTO local(nome, obs, idempresa) VALUES(?,?,?)";
		try
		{
			//Prepara o statement para insercao
			PreparedStatement stmt = connection.prepareStatement(sql);
			
			//seta os dados
			stmt.setString(1, local.getNome());
			stmt.setString(2, local.getObs());
			stmt.setInt(3, local.getCodEmpresa());
						
			stmt.execute();
			stmt.close();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public List<Local> getLista()
	{
		List<Local> locais = new ArrayList<Local>();
		return locais;
	}
	public List<Local> getLista(int codEmpresa)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("SELECT * from local WHERE idempresa=? order by id");
			
			stmt.setInt(1, codEmpresa);
			
			ResultSet rs = stmt.executeQuery();
			List<Local> locais = new ArrayList<Local>();
			
			while(rs.next())
			{
				//Criando um nova instancia
				Local local = new Local();
				local.setCodigo(rs.getInt("id"));
				local.setNome(rs.getString("nome"));
				local.setObs(rs.getString("obs"));
				
				//Adicionando objeto a lista
				locais.add(local);
			}
			
			rs.close();
			stmt.close();
			return locais;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}

	public void altera(Local local)
	{
		String sql = "UPDATE local SET nome=?, obs=? WHERE id=? and idempresa=?";
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, local.getNome());
			stmt.setString(2, local.getObs());
			stmt.setInt(3, local.getCodigo());
			stmt.setInt(4, local.getCodEmpresa());
			
			stmt.execute();
			stmt.close();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public void remove(Local local)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("DELETE from local where id=? and idempresa=?");
			stmt.setLong(1,local.getCodigo());
			stmt.setInt(2, local.getCodEmpresa());
			
			stmt.execute();
			stmt.close();
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
	}
	
	public Local buscaPorCodigo(int codEmpresa, int codigo)
	{
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("SELECT * from local where id=? and idempresa=?");
			
			stmt.setInt(1, codigo);
			stmt.setInt(2, codEmpresa);
			
			ResultSet rs = stmt.executeQuery();
			Local local = new Local();
			if(rs.next())
			{
				//Criando um nova instancia
				local.setCodigo(rs.getInt("id"));
				local.setNome(rs.getString("nome"));
				local.setObs(rs.getString("obs"));
			
			}
	
			rs.close();
			stmt.close();
			return local;
			
		}
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		
	}
	
	
	public Local getLocal(Local local)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from local where nome ILIKE ? and idempresa=?");
			stmt.setString(1, local.getNome());
			stmt.setInt(2, local.getCodEmpresa());
			
			rs=stmt.executeQuery();
			
			if(rs.next())
			{
				local.setAlerta(true);
			}
			
			else
			{
				local.setAlerta(false);
			}
			stmt.close();
			rs.close();
		
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return local;
		
	}
	
	public Local getLocalUp(Local local)
	{
		ResultSet rs;
		try
		{
			PreparedStatement stmt = this.connection.prepareStatement("select nome from local where nome ILIKE ? and idempresa=? and id !=?");
			stmt.setString(1, local.getNome());
			stmt.setInt(2, local.getCodEmpresa());
			stmt.setInt(3, local.getCodigo());
			
			rs=stmt.executeQuery();
			
			if(rs.next())
			{
				local.setAlerta(true);
			}
			
			else
			{
				local.setAlerta(false);
			}
			stmt.close();
			rs.close();
		
		}
		
		catch(SQLException e)
		{
			throw new RuntimeException(e);
		}
		return local;
		
	}
	
}
